﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    /// <summary>
    /// 订单管理
    /// </summary>
    public class OrderBase
    { 
        private SqlConnection Conn;

        public OrderBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }
 
        #region 添加 / 修改
        const string SQL_INSERT_ORDER = "Declare @OrderId Uniqueidentifier; Declare @ERR INT; SELECT @OrderId = NEWID(); INSERT INTO OrderBase VALUES(@OrderId,@UserId,@ShippingAddressId,@OrderDate,@OrderNumber,@ShipAddress,@ShipCounties,@ShipCity,@ShipState,@ShipCountry,@ShipZip,@ShipTel,@ShipMobile,@ShipToName,@PaymentType,@PaymentTypeId,@NeedInvoice,@InvoiceType,@InvoiceTitle,@TotalPrice,@Locale,@StatusCode,@Comment,@CreatedOn,@CreatedBy,@ModifyOn,@ModifyBy,@ShippingCost,@ShippingMethod,@ShippingMethodType,0,'',@OrderTypeCode,@RegardingObjectId); SELECT @ERR=@@ERROR; ";
        const string SQL_INSERT_ORDERITEMS = "INSERT INTO OrderItems([OrderId],[LineNum],[ProductId],[Quantity],[UnitPrice]) VALUES(";
        const string SQL_UPDATE_ORDER = "Declare @ERR INT; UPDATE OrderBase SET [UserId] = @UserId,[ShippingAddressId] = @ShippingAddressId,[OrderDate] = @OrderDate,[OrderNumber] = @OrderNumber,[ShipAddress] = @ShipAddress,[ShipCounties] = @ShipCounties,[ShipCity] = @ShipCity,[ShipState] = @ShipState,[ShipCountry] = @ShipCountry,[ShipZip] = @ShipZip,[ShipTel] = @ShipTel, ShipMobile = @ShipMobile, [ShipToName] = @ShipToName,PaymentType=@PaymentType,PaymentTypeId=@PaymentTypeId,[NeedInvoice] = @NeedInvoice,[InvoiceType] = @InvoiceType,[InvoiceTitle] = @InvoiceTitle,[TotalPrice] = @TotalPrice,[Locale] = @Locale,[StatusCode] = @StatusCode,[Comment] = @Comment,[ModifyOn] = @ModifyOn,[ModifyBy] = @ModifyBy,[ShippingCost] = @ShippingCost,[ShippingMethod] = @ShippingMethod,[ShippingMethodType] = @ShippingMethodType WHERE OrderId = @OrderId; SELECT @ERR=@@ERROR;";
        const string SQL_DELETE_ORDERITEMS = "DELETE FROM OrderItems WHERE OrderId = @OrderId; SELECT @ERR=@@ERROR;";

        /// <summary>
        /// 添加 / 修改订单信息
        /// </summary>
        /// <param name="_complaint"></param>
        /// <returns></returns>
        public string OrderAdmin(Model.OrderBase _order)
        {
            StringBuilder SQL = new StringBuilder();
            SqlParameter[] parameters = GetOrderParameters();
            parameters[0].Value = new Guid(_order.UserId);
            parameters[1].Value = new Guid(_order.ShippingAddressId);
            parameters[2].Value = _order.OrderDate;
            parameters[3].Value = _order.OrderNumber;
            parameters[4].Value = _order.ShipAddress;
            parameters[5].Value = _order.ShipCounties;
            parameters[6].Value = _order.ShipCity;
            parameters[7].Value = _order.ShipState;
            parameters[8].Value = _order.ShipCountry;
            parameters[9].Value = _order.ShipZip;
            parameters[10].Value = _order.ShipTel;
            parameters[11].Value = _order.ShipMobile;
            parameters[12].Value = _order.ShipToName;
            parameters[13].Value = _order.PaymentType;
            parameters[14].Value = _order.PaymentTypeId;
            parameters[15].Value = _order.NeedInvoice;
            parameters[16].Value = _order.InvoiceType;
            parameters[17].Value = _order.InvoiceTitle;
            parameters[18].Value = _order.TotalPrice;
            parameters[19].Value = _order.Locale;
            parameters[20].Value = _order.StatusCode;
            parameters[21].Value = _order.Comment;
            parameters[22].Value = _order.CreatedOn;
            parameters[23].Value = _order.CreatedBy;
            parameters[24].Value = _order.ModifyOn;
            parameters[25].Value = _order.ModifyBy;
            parameters[26].Value = _order.ShippingCost;
            parameters[27].Value = _order.ShippingMethod;
            parameters[28].Value = _order.ShippingMethodType;
            parameters[29].Value = _order.OrderTypeCode;
            parameters[30].Value = new Guid(_order.RegardingObjectId);

            SqlCommand cmd = new SqlCommand();

            foreach (SqlParameter parm in parameters)
                cmd.Parameters.Add(parm);

            string result = "";
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                ///设置隔离级别,启动数据库事务
                SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                if (_order.OrderId == "{00000000-0000-0000-0000-000000000000}")
                {
                    SQL.Append(SQL_INSERT_ORDER);
                }
                else
                {
                    SQL.AppendFormat("Declare @OrderId Uniqueidentifier; SELECT @OrderId = '{0}';", _order.OrderId);
                    SQL.Append(SQL_UPDATE_ORDER);
                    SQL.Append(SQL_DELETE_ORDERITEMS);
                }

                try
                {
                    SqlParameter[] orderitemsParms = null;

                    int i = 0;
                    if (_order.OrderItems != null && _order.OrderItems.Length > 0)
                    {
                        foreach (Model.OrderItems _items in _order.OrderItems)
                        {
                            orderitemsParms = GetOrderItemsParameters(i);
                            orderitemsParms[0].Value = _items.LineNum;
                            orderitemsParms[1].Value = new Guid(_items.ProductId);
                            orderitemsParms[2].Value = _items.Quantity;
                            orderitemsParms[3].Value = _items.UnitPrice;

                            SQL.Append(SQL_INSERT_ORDERITEMS).Append("@OrderId").Append(", @LineNum").Append(i).Append(", @ProductId").Append(i).Append(", @Quantity").Append(i).Append(", @UnitPrice").Append(i).Append("); SELECT @ERR=@@ERROR;");

                            //遍历绑定参数
                            foreach (SqlParameter parm in orderitemsParms)
                                cmd.Parameters.Add(parm);

                            i++;
                        }
                    }

                    if (conn.State.ToString() != "Open")
                        conn.Open();

                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = trans.Connection;
                    cmd.Transaction = trans;

                    cmd.CommandText = SQL.Append("SELECT @ERR, @OrderId;").ToString();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        rdr.Read();
                        // 如果错误的数量不为零, 抛出异常终止操作
                        if (rdr.GetInt32(0) != 0)
                        {
                            trans.Rollback();
                        }
                        else
                        {
                            result = "succeeded" + "|" + rdr.GetGuid(1).ToString();
                        }

                        rdr.Dispose();
                    }
                    ///提交事务
                    trans.Commit();
                    cmd.Parameters.Clear();
                }
                catch (Exception e)
                {
                    result = "faild";
                    ///事务回滚
                    trans.Rollback();
                }
                finally
                {
                    SQL = null;
                    conn.Close();
                    conn.Dispose();
                }
            }

            return result;
        }
        /// <summary>
        /// 初始化参数, 添加模式 
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetOrderParameters()
        {
            SqlParameter[] parms = null;

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@ShippingAddressId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@OrderDate", SqlDbType.DateTime),
					new SqlParameter("@OrderNumber", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipAddress", SqlDbType.NVarChar,512),
					new SqlParameter("@ShipCounties", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipCity", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipState", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipCountry", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipZip", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipTel", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipMobile", SqlDbType.VarChar, 256),
					new SqlParameter("@ShipToName", SqlDbType.VarChar, 256),
					new SqlParameter("@PaymentType", SqlDbType.VarChar, 256),
					new SqlParameter("@PaymentTypeId", SqlDbType.Int, 4),
					new SqlParameter("@NeedInvoice", SqlDbType.Bit),
					new SqlParameter("@InvoiceType", SqlDbType.VarChar, 64),
					new SqlParameter("@InvoiceTitle", SqlDbType.VarChar, 512),
					new SqlParameter("@TotalPrice", SqlDbType.Decimal),
					new SqlParameter("@Locale", SqlDbType.VarChar, 64),
					new SqlParameter("@StatusCode", SqlDbType.Int),
					new SqlParameter("@Comment", SqlDbType.NText, 1024),
					new SqlParameter("@CreatedOn", SqlDbType.DateTime),
					new SqlParameter("@CreatedBy", SqlDbType.VarChar, 256),
					new SqlParameter("@ModifyOn", SqlDbType.DateTime),
					new SqlParameter("@ModifyBy", SqlDbType.VarChar, 256),
					new SqlParameter("@ShippingCost", SqlDbType.Decimal),
					new SqlParameter("@ShippingMethod", SqlDbType.VarChar, 512),
					new SqlParameter("@ShippingMethodType", SqlDbType.Int),
					new SqlParameter("@OrderTypeCode", SqlDbType.Int),
					new SqlParameter("@RegardingObjectId", SqlDbType.UniqueIdentifier)
                };
            }

            return parms;
        }
        /// <summary>
        /// 初始化参数序列 
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetOrderItemsParameters(int i)
        {
            SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_ORDERITEMS + i);

            if (parms == null)
            {
                parms = new SqlParameter[] { 
					new SqlParameter("@LineNum" + i, SqlDbType.Int),
                    new SqlParameter("@ProductId" + i, SqlDbType.UniqueIdentifier, 256),
                    new SqlParameter("@Quantity" + i, SqlDbType.Int),
                    new SqlParameter("@UnitPrice" + i, SqlDbType.Decimal), 
                };

                SqlHelper.CacheParameters(SQL_INSERT_ORDERITEMS + i, parms);
            }

            return parms;
        }
        #endregion

        #region 查询
        /// <summary>
        /// 匹配ID获取订单实体信息
        /// </summary>
        public Model.OrderBase GetObjectById(string orderId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM OrderBase WHERE [OrderId] = @OrderId");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@OrderId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(orderId);

            Model.OrderBase _order = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _order = new Model.OrderBase();
                    _order.OrderId = rd["OrderId"].ToString();
                    _order.UserId = rd["UserId"].ToString();
                    _order.ShippingAddressId = rd["ShippingAddressId"].ToString(); 
                    _order.OrderDate = DateTime.Parse(rd["OrderDate"].ToString());
                    _order.OrderNumber = rd["OrderNumber"].ToString();
                    _order.ShipAddress = rd["ShipAddress"].ToString();
                    _order.ShipCounties = rd["ShipCounties"].ToString();
                    _order.ShipCity = rd["ShipCity"].ToString();
                    _order.ShipState = rd["ShipState"].ToString();
                    _order.ShipCountry = rd["ShipCountry"].ToString();
                    _order.ShipZip = rd["ShipZip"].ToString();
                    _order.ShipTel = rd["ShipTel"].ToString();
                    _order.ShipMobile = rd["ShipMobile"].ToString();
                    _order.ShipToName = rd["ShipToName"].ToString();
                    _order.PaymentType = rd["PaymentType"].ToString();
                    _order.PaymentTypeId = int.Parse(rd["PaymentTypeId"].ToString());  
                    _order.NeedInvoice = bool.Parse(rd["NeedInvoice"].ToString()); 
                    _order.InvoiceType = rd["InvoiceType"].ToString();
                    _order.InvoiceTitle = rd["InvoiceTitle"].ToString();
                    _order.TotalPrice = decimal.Parse(rd["TotalPrice"].ToString());
                    _order.Locale = rd["Locale"].ToString();
                    _order.StatusCode = int.Parse(rd["StatusCode"].ToString());
                    _order.Comment = rd["Comment"].ToString();
                    _order.CreatedOn = DateTime.Parse(rd["CreatedOn"].ToString());
                    _order.CreatedBy = rd["CreatedBy"].ToString();
                    _order.ModifyOn = DateTime.Parse(rd["ModifyOn"].ToString());
                    _order.ModifyBy = rd["ModifyBy"].ToString();
                    _order.ShippingCost = decimal.Parse(rd["ShippingCost"].ToString());
                    _order.ShippingMethod = rd["ShippingMethod"].ToString();
                    _order.ShippingMethodType = int.Parse(rd["ShippingMethodType"].ToString());
                    _order.OrderTypeCode = int.Parse(rd["OrderTypeCode"].ToString());
                    _order.RegardingObjectId = rd["RegardingObjectId"].ToString();
                    _order.CancelReason = rd["CancelReason"].ToString();
                    _order.CancelReasonCode = int.Parse(rd["CancelReasonCode"].ToString());
                    
                    if (_order.PaymentTypeId != 0)
                    {
                        parameters = new SqlParameter[] { new SqlParameter("@PaymentTypeId", SqlDbType.Int) };
                        parameters[0].Value = _order.PaymentTypeId;

                        SqlDataReader rd1 = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "SELECT * FROM PaymentTypeBase WHERE [PaymentTypeId] = @PaymentTypeId", parameters);
                        if (rd1.Read())
                        {
                            _order.PaymentTypeInfo = new Model.PaymentTypeBase(_order.PaymentTypeId, rd1["Name"].ToString(), rd1["Brand"].ToString(), rd1["Description"].ToString(), int.Parse(rd1["TypeCode"].ToString()), rd1["WebsiteURL"].ToString());
                        }
                        rd1.Close();
                        rd1.Dispose();
                    }


                    IList<Model.OrderItems> _items = GetItems(_order.OrderId);
                    _order.OrderItems = new Model.OrderItems[_items.Count];
                    GetItems(_order.OrderId).CopyTo(_order.OrderItems, 0);
                }
                rd.Close();
                rd.Dispose();
            }
            catch
            {
                return null;
            }
            return _order;
        }

        /// <summary>
        /// 匹配订单号获取订单实体信息
        /// </summary>
        public Model.OrderBase GetObjectByOrderNumber(string ordernumber, string userid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM OrderBase WHERE [OrderNumber] = @OrderNumber AND UserId = @UserId");

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@OrderNumber", SqlDbType.VarChar, 200),
                new SqlParameter("@UserId", SqlDbType.UniqueIdentifier, 16)
            };
            parameters[0].Value = ordernumber;
            parameters[1].Value = new Guid(userid);

            Model.OrderBase _order = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _order = new Model.OrderBase();
                    _order.OrderId = rd["OrderId"].ToString();
                    _order.UserId = rd["UserId"].ToString();
                    _order.ShippingAddressId = rd["ShippingAddressId"].ToString();
                    _order.OrderDate = DateTime.Parse(rd["OrderDate"].ToString());
                    _order.OrderNumber = rd["OrderNumber"].ToString();
                    _order.ShipAddress = rd["ShipAddress"].ToString();
                    _order.ShipCounties = rd["ShipCounties"].ToString();
                    _order.ShipCity = rd["ShipCity"].ToString();
                    _order.ShipState = rd["ShipState"].ToString();
                    _order.ShipCountry = rd["ShipCountry"].ToString();
                    _order.ShipZip = rd["ShipZip"].ToString();
                    _order.ShipTel = rd["ShipTel"].ToString();
                    _order.ShipMobile = rd["ShipMobile"].ToString();
                    _order.ShipToName = rd["ShipToName"].ToString();
                    _order.PaymentType = rd["PaymentType"].ToString();
                    _order.PaymentTypeId = int.Parse(rd["PaymentTypeId"].ToString());
                    _order.NeedInvoice = bool.Parse(rd["NeedInvoice"].ToString());
                    _order.InvoiceType = rd["InvoiceType"].ToString();
                    _order.InvoiceTitle = rd["InvoiceTitle"].ToString();
                    _order.TotalPrice = decimal.Parse(rd["TotalPrice"].ToString());
                    _order.Locale = rd["Locale"].ToString();
                    _order.StatusCode = int.Parse(rd["StatusCode"].ToString());
                    _order.Comment = rd["Comment"].ToString();
                    _order.CreatedOn = DateTime.Parse(rd["CreatedOn"].ToString());
                    _order.CreatedBy = rd["CreatedBy"].ToString();
                    _order.ModifyOn = DateTime.Parse(rd["ModifyOn"].ToString());
                    _order.ModifyBy = rd["ModifyBy"].ToString();
                    _order.ShippingCost = decimal.Parse(rd["ShippingCost"].ToString());
                    _order.ShippingMethod = rd["ShippingMethod"].ToString();
                    _order.ShippingMethodType = int.Parse(rd["ShippingMethodType"].ToString());
                    _order.CancelReason = rd["CancelReason"].ToString();
                    _order.CancelReasonCode = int.Parse(rd["CancelReasonCode"].ToString());
                    _order.OrderTypeCode = int.Parse(rd["OrderTypeCode"].ToString());
                    _order.RegardingObjectId = rd["RegardingObjectId"].ToString();

                    if (_order.PaymentTypeId != 0)
                    {
                        parameters = new SqlParameter[] { new SqlParameter("@PaymentTypeId", SqlDbType.Int) };
                        parameters[0].Value = _order.PaymentTypeId;

                        SqlDataReader rd1 = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "SELECT * FROM PaymentTypeBase WHERE [PaymentTypeId] = @PaymentTypeId", parameters);
                        if (rd1.Read())
                        {
                            _order.PaymentTypeInfo = new Model.PaymentTypeBase(_order.PaymentTypeId, rd1["Name"].ToString(), rd1["Brand"].ToString(), rd1["Description"].ToString(), int.Parse(rd1["TypeCode"].ToString()), rd1["WebsiteURL"].ToString());
                        }
                        rd1.Close();
                        rd1.Dispose();
                    }

                    IList<Model.OrderItems> _items = GetItems(_order.OrderId);
                    _order.OrderItems = new Model.OrderItems[_items.Count];
                    GetItems(_order.OrderId).CopyTo(_order.OrderItems, 0);
                }
                rd.Close();
                rd.Dispose();
            }
            catch
            {
                return null;
            }
            return _order;
        }

        /// <summary>
        /// 匹配 orderId 获取订单明细
        /// </summary>
        /// <param name="complaintId"></param>
        /// <returns></returns>
        public IList<Model.OrderItems> GetItems(string orderId)
        {
            string SQL = "SELECT * FROM vw_OrderItems WHERE OrderId = @OrderId ORDER BY LineNum ASC";
            SqlParameter[] Params = new SqlParameter[] { new SqlParameter("@OrderId", SqlDbType.UniqueIdentifier) };
            Params[0].Value = new Guid(orderId);

            IList<Model.OrderItems> _items = new List<Model.OrderItems>(); 
            Model.OrderItems _item = null;
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL, Params);
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Select(""))
                    {
                        _item = new Model.OrderItems(dr["OrderId"].ToString(), int.Parse(dr["LineNum"].ToString()), int.Parse(dr["Quantity"].ToString()), decimal.Parse(dr["UnitPrice"].ToString()), dr["ProductName"].ToString(), dr["ProductId"].ToString());
                        _items.Add(_item); 
                    }

                    dt.Clear();
                    dt.Dispose();
                }
            }
            catch
            {
                return null;
            }

            return _items;
        }
        #endregion

        #region 设置订单状态
        /// <summary>
        /// 设置订单状态
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public string CancelOrder(string orderid, string userid, int reasoncode, string reason)
        {
            SqlParameter[]  Parms = new SqlParameter[] { 
                new SqlParameter("@CancelReasonCode",SqlDbType.Int),
                new SqlParameter("@CancelReason",SqlDbType.NVarChar),
                new SqlParameter("@UserId",SqlDbType.UniqueIdentifier),
                new SqlParameter("@OrderId",SqlDbType.UniqueIdentifier),
                new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = reasoncode;
            Parms[1].Value = reason;
            Parms[2].Value = new Guid(userid);
            Parms[3].Value = new Guid(orderid);
            Parms[4].Direction = ParameterDirection.ReturnValue;

            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "OrderBase_CancelOrder", Parms).ToString();

                int status = ((Parms[4].Value != null) ? ((int)Parms[4].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded"; 
                }
                else if (status == 1)
                {
                    result = "单据不存在!"; ///exists;
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;

        }

        public string ExchangeConfirm(string userId, string orderId, int integralCount)
        {
            SqlParameter[] Parms = new SqlParameter[] {  
                    new SqlParameter("@UserId",SqlDbType.UniqueIdentifier), 
                    new SqlParameter("@IntegralCount",SqlDbType.Int),
                    new SqlParameter("@OrderId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            Parms[0].Value = new Guid(userId);
            Parms[1].Value = integralCount;
            Parms[2].Value = new Guid(orderId);
            Parms[3].Direction = ParameterDirection.ReturnValue; 

            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "IntegralBase_PayOut", Parms);
                int status = ((Parms[3].Value != null) ? ((int)Parms[3].Value) : -1);
                switch (status)
                {
                    case 0: 
                        result = "succeeded";
                        break;
                    case 2:
                        result = "对不起，您的可用积分不够，不能兑换！";
                        break;
                    default:
                        result = "系统错误！";
                        break;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result; 
        }

        /// <summary>
        /// 修改订单状态
        /// </summary> 
        /// <returns></returns>
        public string UpdateStatus(string ordernumber, string modifyby, int status)
        {
            string result = "";
            SqlParameter[] parameters = new SqlParameter[] {   
				new SqlParameter("@ModifyBy", SqlDbType.UniqueIdentifier),  
				new SqlParameter("@StatusCode", SqlDbType.Int),  
				new SqlParameter("@OrderNumber", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = new Guid(modifyby);
            parameters[1].Value = status;
            parameters[2].Value = ordernumber;

            try
            {
                StringBuilder SQL = new StringBuilder();
                SQL.Append("UPDATE OrderBase SET ModifyBy = @ModifyBy, ModifyOn = GETDATE(), StatusCode = @StatusCode WHERE OrderNumber = @OrderNumber;");
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL.ToString(), parameters);
                
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message; ///procedure error
            }

            return result;
        }
        #endregion

    }
}
